﻿

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;
using ZLDC.Topo.Model.Estate;

namespace ZLDC.DAL.Estate
{
                public class EstateDAL
    {
                                                                                                                                                                                                                                                                public static void p_estate_save(int id, string name, string roadName, string otherName, string namePy, string otherNamePy, int cityId, string cityName, int areaId, string areaName, string address, string usage, string type, float constructionArea, string construction, int familyTotal, string propertyLevel, string propertyCompany, string propertyTel, float propertyFare, float salesFare, string realestateName, string realestateTel, int registerUserid, string registerName, int registerDeptid, string registerDept, string updateName, string aroundCondition, string trafficCondition, string remark, string completeDate, int isvalid, string leasetime, string park, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));
            parms.Add(new SqlParameter("@name", name));
            parms.Add(new SqlParameter("@roadName", roadName));
            parms.Add(new SqlParameter("@otherName", otherName));
            parms.Add(new SqlParameter("@namePy", namePy));
            parms.Add(new SqlParameter("@otherNamePy", otherNamePy));
            parms.Add(new SqlParameter("@cityId", cityId));
            parms.Add(new SqlParameter("@cityName", cityName));
            parms.Add(new SqlParameter("@areaId", areaId));
            parms.Add(new SqlParameter("@areaName", areaName));
            parms.Add(new SqlParameter("@address", address));
            parms.Add(new SqlParameter("@usage", usage));
            parms.Add(new SqlParameter("@type", type));
            parms.Add(new SqlParameter("@constructionArea", constructionArea));
            parms.Add(new SqlParameter("@construction", construction));
            parms.Add(new SqlParameter("@familyTotal", familyTotal));
            parms.Add(new SqlParameter("@propertyLevel", propertyLevel));
            parms.Add(new SqlParameter("@propertyCompany", propertyCompany));
            parms.Add(new SqlParameter("@propertyTel", propertyTel));
            parms.Add(new SqlParameter("@propertyFare", propertyFare));
            parms.Add(new SqlParameter("@salesFare", salesFare));
            parms.Add(new SqlParameter("@realestateName", realestateName));
            parms.Add(new SqlParameter("@realestateTel", realestateTel));
            parms.Add(new SqlParameter("@registerUserid", registerUserid));
            parms.Add(new SqlParameter("@registerName", registerName));
            parms.Add(new SqlParameter("@registerDeptid", registerDeptid));
            parms.Add(new SqlParameter("@registerDept", registerDept));
            parms.Add(new SqlParameter("@updateName", updateName));
            parms.Add(new SqlParameter("@aroundCondition", aroundCondition));
            parms.Add(new SqlParameter("@trafficCondition", trafficCondition));
            parms.Add(new SqlParameter("@remark", remark));
            parms.Add(new SqlParameter("@completeDate", completeDate));
            parms.Add(new SqlParameter("@isvalid", isvalid));
            parms.Add(new SqlParameter("@leasetime", leasetime));
            parms.Add(new SqlParameter("@park", park));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_estate_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

                                                                                public static DataTable p_estate_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_estate_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                                                        public static DataTable p_estate_list_action_table(int userid, int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@userid", userid));
            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_estate_list_action", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
   
                                                        public static DataTable p_estate_detail_table(int id, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_estate_detail", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                        public static EstateInfo p_estate_detail(int id, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@id", id));
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.fetch_procedure("p_estate_detail", parms);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            if (db.num_rows == 0)
            {
                return null;
            }
            else
            {
                EstateInfo c = new EstateInfo();
                c.DelFlag = (db.rows[0]["delFlag"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["delFlag"].ToString());
                c.IsValid = (db.rows[0]["isValid"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["isValid"].ToString());
                c.FamilyTotal = (db.rows[0]["familyTotal"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["familyTotal"].ToString());
                c.Id = (db.rows[0]["id"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["id"].ToString());
                c.CityId = (db.rows[0]["cityId"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["cityId"].ToString());
                c.Areaid = (db.rows[0]["areaid"] == System.DBNull.Value) ? 0 : Convert.ToInt32(db.rows[0]["areaid"].ToString());
                c.CompleteDate = (db.rows[0]["completeDate"] == System.DBNull.Value) ? "" : db.rows[0]["completeDate"].ToString();   
                c.RegisterDate = (db.rows[0]["registerDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["registerDate"].ToString());
                c.UpdateDate = (db.rows[0]["updateDate"] == System.DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(db.rows[0]["updateDate"].ToString());
                c.ConstructionArea = (db.rows[0]["constructionArea"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["constructionArea"].ToString());
                c.PropertyFare = (db.rows[0]["propertyFare"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["propertyFare"].ToString());
                c.SalesFare = (db.rows[0]["salesFare"] == System.DBNull.Value) ? 0 : Convert.ToSingle(db.rows[0]["salesFare"].ToString());
                c.Name = (db.rows[0]["name"] == System.DBNull.Value) ? "" : db.rows[0]["name"].ToString();
                c.OtherName = (db.rows[0]["otherName"] == System.DBNull.Value) ? "" : db.rows[0]["otherName"].ToString();
                c.NamePy = (db.rows[0]["namePy"] == System.DBNull.Value) ? "" : db.rows[0]["namePy"].ToString();
                c.OtherNamePy = (db.rows[0]["otherNamePy"] == System.DBNull.Value) ? "" : db.rows[0]["otherNamePy"].ToString();
                c.CityName = (db.rows[0]["cityName"] == System.DBNull.Value) ? "" : db.rows[0]["cityName"].ToString();
                c.AreaName = (db.rows[0]["areaName"] == System.DBNull.Value) ? "" : db.rows[0]["areaName"].ToString();
                c.TrafficCondition = (db.rows[0]["trafficCondition"] == System.DBNull.Value) ? "" : db.rows[0]["trafficCondition"].ToString();
                c.Remark = (db.rows[0]["remark"] == System.DBNull.Value) ? "" : db.rows[0]["remark"].ToString();
                c.PropertyTel = (db.rows[0]["propertyTel"] == System.DBNull.Value) ? "" : db.rows[0]["propertyTel"].ToString();
                c.RealestateName = (db.rows[0]["realestateName"] == System.DBNull.Value) ? "" : db.rows[0]["realestateName"].ToString();
                c.RealestateTel = (db.rows[0]["realestateTel"] == System.DBNull.Value) ? "" : db.rows[0]["realestateTel"].ToString();
                c.RegisterName = (db.rows[0]["registerName"] == System.DBNull.Value) ? "" : db.rows[0]["registerName"].ToString();
                c.UpdateName = (db.rows[0]["updateName"] == System.DBNull.Value) ? "" : db.rows[0]["updateName"].ToString();
                c.AroundCondition = (db.rows[0]["aroundCondition"] == System.DBNull.Value) ? "" : db.rows[0]["aroundCondition"].ToString();
                c.Address = (db.rows[0]["address"] == System.DBNull.Value) ? "" : db.rows[0]["address"].ToString();
                c.Usage = (db.rows[0]["usage"] == System.DBNull.Value) ? "" : db.rows[0]["usage"].ToString();
                c.Type = (db.rows[0]["type"] == System.DBNull.Value) ? "" : db.rows[0]["type"].ToString();
                c.Construction = (db.rows[0]["construction"] == System.DBNull.Value) ? "" : db.rows[0]["construction"].ToString();
                c.PropertyLevel = (db.rows[0]["propertyLevel"] == System.DBNull.Value) ? "" : db.rows[0]["propertyLevel"].ToString();
                c.PropertyCompany = (db.rows[0]["propertyCompany"] == System.DBNull.Value) ? "" : db.rows[0]["propertyCompany"].ToString();
                return c;
            }
        }

                                                public static int EstateCheck(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string userName = _Utility.GetInfoBySession().m_sUserName;
                string sql = "update t_estate set isValid = 0,updateDate='" + DateTime.Now.ToString() + "',updateName='" + userName + "' where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                public static int EstateCheckAll(int eid, _Database db)
        {
            int rows = 0;

            try
            {                
                string sql = "update t_roomno set isValid = 0 where delFlag = 0 and bid in(select id from t_build where eid = " + eid + ");";
                sql += "update t_build set isValid = 0 where delFlag = 0 and eid = " + eid;

                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                public static int EstateCheck_CS(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string userName = _Utility.GetInfoBySession().m_sUserName;
                string sql = "update t_estate set isValid = 3,updateDate='" + DateTime.Now.ToString() + "',updateName='" + userName + "' where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                public static int EstateCheck(int id, int isvalid, string Approval_views, _Database db)
        {
            int rows = 0;

            try
            {
                string userName = _Utility.GetInfoBySession().m_sUserName;
                string sql = "update t_estate set isValid = " + isvalid + ",updateDate='" + DateTime.Now.ToString() + "',updateName='" + userName + "',Approvalviews='" + Approval_views+ "' where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }



                                                public static int EstateDel(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_estate set delFlag = 1 where id=" + id + "";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                public static int EstateDelAll(int eid, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_roomno set delFlag = 1 where delFlag = 0 and bid in(select id from t_build where eid = " + eid + ");";
                sql += "update t_build set delFlag = 1 where delFlag = 0 and eid = " + eid;

                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

        public static int EstateDelAndIsValid(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_estate set delFlag = 1 where id=" + id + " and isvalid>0";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                                
                                                                
                
                                                                public static DataTable p_estate_list_table(int top, string condition, _Database db)
        {
            DataTable dt = null;
            DataSet ds = null;

            try
            {
                string sql = "select top " + top + " id, name, roadName, cityId, areaid, areaName,cityName, type from t_estate " + condition;                
                ds = db.Query(sql);

                if (ds != null && ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            catch { }
            finally { db.close(); }

            return dt;
        }

                                                                                                public static int UpdateOtherNameById(string id, string oname, string type, string tablename, string commitName, string commitDept, string commitDeptID)
        {
            int row = 0;
            using (_Database db = new _Database("estate"))
            {
                if (type == "")
                {
                    string sql = "update t_estate set otherName='" + oname + "' where id=" + id + "";
                    row = db.ExecuteSql(sql);
                }
                else if (type != "")
                {
                    string sql = "insert into t_estate_other_name(type,tableName,tableId,modifyName,commitName,commitDept,commitDeptid,commitDate) values(" + type + ",'" + tablename + "'," + id + ",'" + oname + "','" + commitName + "','" + commitDept + "','" + commitDeptID + "',GETDATE())";
                    row = db.ExecuteSql(sql);
                }
            }

            return row;
        }

                                                public static string SelectNameByEid(int id)
        {
            DataSet ds = null;
            string ename = string.Empty;
            using (_Database db = new _Database("estate"))
            {
                string name = string.Empty;

                string sql = "select name from t_estate where id=" + id + " and delFlag=0";
                ds = db.Query(sql);
                ename = ds.Tables[0].Rows[0]["name"].ToString();
            }

            return ename;
        }

                public static string SelectIdByName(string NAME) 
        {
            DataSet ds = null;
            string ename = string.Empty;
            string estateid = string.Empty;

            using (_Database db = new _Database("estate"))
            {                
                string[] splitstr;
                splitstr = NAME.Split(',');
                string strEname = "";
                for (int i = 0; i < splitstr.Length; i++)
                {
                    strEname += "'" + splitstr[i] + "',";
                }
                strEname = strEname.Substring(0, strEname.Length - 1);
                string sql = "SELECT * FROM T_ESTATE WHERE NAME IN(" + strEname + ")";
                ds = db.Query(sql);


                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    estateid += ds.Tables[0].Rows[0]["id"].ToString() + ",";
                }
                if (ds.Tables[0].Rows.Count > 0)
                    estateid = estateid.Substring(0, estateid.Length - 1);
                else
                    estateid = "0";
            }

            return estateid;
        }

                                                        public static int DeleteEstate(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "delete from t_estate where  id =" + id + " and delflag=1 ";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                public static int UpdateDelFlag (int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update  t_estate set delflag=0 where  id =" + id + " and delflag=1 ";
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                        public static int UpateEstate(int id, _Database db)
        {
            int rows = 0;

            try
            {
                string sql = "update t_estate set delflag=0, isvalid=1 where  id =" + id;
                rows = db.ExecuteSql(sql);
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
    }
}
